package com.eascs.admin.o2o.oaotodata.dao;

import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.eascs.admin.common.dao.BaseOaDao;
import com.eascs.admin.o2o.oaotodata.entity.OaO2oLoanapply;

/**
* @author bao.pan 2016年12月23日 上午9:54:59
*/
@Repository
public class OaComDataDao extends BaseOaDao{
	public List<OaO2oLoanapply> findoaORo2odateLoanapply(int num){
		StringBuffer sql=new StringBuffer();
		sql.append("select * from(");
		sql.append("select * from (");
		sql.append("select r.Bankloanamount as bankAmount,r.BANKREFUNDCODE as bankLoanName,r.LOADDATE as paymentTime,t.billstatus as is_place_on_file,l.idcard,");
		sql.append("s.unitcode as ltdCode,s.namecn as ltdName ,");
		sql.append("f.notation as notation,f.type as type,f.createdate as backTime,");
		sql.append("CASE WHEN r.Loaddate is null THEN 'SN' ELSE 'O' END as wfType,0 as payeaid,");
		sql.append(" y.* from eadb.o2o_loanapply y LEFT JOIN eadb.O2O_FEEDBACK f ON y.bank_applyno=f.appno,eadb.o2o_refund r,eadb.O2O_REFUNDCONTRACT t,eadb.o2o_loaner l,eadb.hr_units s");
		sql.append(" where y.loanapplyno=r.loanapplyno ");
		sql.append(" and r.refundno=t.refundno and  y.loanapplyno=l.loanapplyno and s.unitno=y.ltd");
		sql.append(" and y.loanapplyno_main is  null and y.islock='N' and r.islock='N'");
		sql.append(" and t.islock='N' and t.isfrom='CON'");
		sql.append(" and y.loan_type='BK' ");
		sql.append("UNION  ");
		sql.append("select ");
		sql.append("y.loanamount as bankAmount,y.banktype as bankLoanName,p.paydate as paymentTime,t.billstatus as is_place_on_file,l.idcard,");
		sql.append("s.unitcode as ltdCode,s.namecn as ltdName ,");
		sql.append("f.notation as notation,f.type as type,f.createdate as backTime,");
		sql.append("case(p.billstatus) when 'CLO' then 'O' else '' end as wfType,p.eaid as payeaid,");
		sql.append("y.* from eadb.o2o_loanapply y LEFT JOIN eadb.O2O_FEEDBACK f ON y.bank_applyno=f.appno left join eadb.o2o_pay p on y.loanapplyno=p.loanapplyid and p.isdel='N',");
		sql.append("eadb.o2o_refund r,eadb.O2O_REFUNDCONTRACT t,eadb.o2o_loaner l,eadb.hr_units s");
		sql.append(" where y.loanapplyno=r.loanapplyno ");
		sql.append(" and r.refundno=t.refundno  and y.loanapplyno=l.loanapplyno and s.unitno=y.ltd");
//		sql.append(" and y.loanapplyno=p.loanapplyid");
		sql.append(" and y.loanapplyno_main is  null and y.islock='N' and r.islock='N'");
		sql.append(" and t.islock='N' and t.isfrom='CON' and y.loan_type<>'BK'");
		sql.append("  ) t order by loanapplyno asc");
		sql.append("  ) where loanapplyno > "+num+" ");
		List<OaO2oLoanapply> list=this.findEntitys(sql.toString(), OaO2oLoanapply.class);
		return list;
	}
	
	public String findpaydate(int eaid){
		StringBuffer sql=new StringBuffer();
		sql.append("select AUDITORDATE from (");
		sql.append("select AUDITORDATE from eadb.wf_processtaskhistory w where w.billno=? and w.billtype='OTP' and w.AUDITORDATE is not null ");
		sql.append("order by w.taskno desc ) where ROWNUM = 1");
		return this.findString(sql.toString(), eaid);
	}
}
